package com.example.yan.excel_split.controller;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.exceptions.ExceptionUtil;
import cn.hutool.core.io.file.FileReader;
import cn.hutool.json.JSONArray;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;


@Controller
@Slf4j
@RequestMapping("/user")
public class UserController {


    @RequestMapping("/abc")
    public String abc() {
        return "file";
    }


    @RequestMapping(value = "/file")
    @ResponseBody
    public Map<String, Object> file(MultipartFile file, HttpServletResponse response) throws IOException {
        // 返回一个layui可识别的返回值 先默认成功吧 后面需要处理上传失败或者处理失败的情况
        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("code", 0);// 0-表示成功
        resultMap.put("msg", "上传成功");
        resultMap.put("count", 1);

        log.info("开始上传附件");
        //默认UTF-8编码，可以在构造中传入第二个参数做为编码
        ExcelReader reader = null;
        try {
            //解析导入的文件内容
            reader = ExcelUtil.getReader(file.getInputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }


        List<List<Object>> list = reader.read();

        String header = null;
        for (List<Object> objects : list) {
            // 迭代删除空单元格
            Iterator<Object> it = objects.iterator();
            while (it.hasNext()) {
                Object cellValue = it.next();
                if (cellValue == null || StringUtils.isBlank(cellValue.toString())) {
                    it.remove();
                }
            }

            String merchantCode = JSON.toJSONString(objects);

            if (merchantCode.contains("商户编号") && merchantCode.contains("商户地址")) {
                System.out.println("merchantCode==>" + merchantCode);
                //把所有的中括号取消
                String str = merchantCode.substring(merchantCode.indexOf("[") + 1, merchantCode.lastIndexOf("]"));
                String st = "商户/部门编号:" + str.substring(12, 26);
                header = st;
            }

        }

        // 我们想要解析出来的表头
        String[] keyArr = new String[]{"终端号", "发卡行", "卡种", "序列号", "交易日期", "交易时间", "交易类型", "授权号", "交易金额", "小费", "分期期数", "银行手续费", "DCC返还手续费", "划账金额", "凭证号", "批次号", "POS交易序号",
                "结算账号", "订单号", "柜台编号", "系统参考号", "持卡人姓名", "付款凭证号", "备注1", "备注2"};
        JSONArray dataList = new JSONArray();
        for (List<Object> aLineList : list) {
            if (aLineList.size() < keyArr.length)// 数据不满足表头长度的不要，说明该行数据不符合我们想要的格式
                continue;
            Map<String, Object> hashMap = new HashMap<>();
            for (int j = 0; j < keyArr.length; j++) {
                if (j < aLineList.size()) {
                    Object property = aLineList.get(j);
                    hashMap.put(keyArr[j], property);
                }
            }
            dataList.add(hashMap);
        }
        System.out.println("解析之后所有符合条件的数据列表===" + dataList);
        // 终端编号的数组，最终得到一个去重的编号组

        List<String> noList = new ArrayList<>();
        for (Object objects : dataList) {
            JSONObject jsonObject = JSON.parseObject(objects.toString());
            String no = jsonObject.getString("终端号");
            if (StringUtils.isNotBlank(no)) {
                // 去除表头，只要终端编号
                if (!no.equals("终端号")) {
                    noList.add(no);
                }
            }
        }
        double totalNew = 0;
        double totalNewB = 0;
        double totalNewC = 0;
        BigDecimal totaA = new BigDecimal("0");
        BigDecimal totaB = new BigDecimal("0");
        BigDecimal totaC = new BigDecimal("0");
        BigDecimal total1 = new BigDecimal("0");
        for (Object objects1 : dataList) {
            Map jsonObject = JSON.parseObject(objects1.toString());

            String jylx = jsonObject.get("交易类型").toString();
            switch (jylx) {

                case "消费":
                    String amount = jsonObject.get("交易金额").toString();
                    totaA = total1.add(new BigDecimal(amount));
                    System.out.println("totaA==>" + totaA);
                    totalNew += Double.parseDouble(amount);
                    totaA = new BigDecimal(totalNew);
                    break;
                case "银联二维码消费(被扫)":
                    String amount1 = jsonObject.get("交易金额").toString();
                    totalNewB += Double.parseDouble(amount1);
                    totaB = new BigDecimal(totalNewB);
                    break;
                case "外卡消费":
                    String amount2 = jsonObject.get("交易金额").toString();
                    totalNewC += Double.parseDouble(amount2);
                    totaC = new BigDecimal(totalNewC);
                    break;
            }


        }

        System.out.println("-----" + totalNew);

        Integer i = 1;
        // 编号去重
        if (noList.size() > 0) {
            List<String> newNoList = new ArrayList<>(new TreeSet<>(noList));
            if (newNoList.size() > 0) {
                System.out.println("去重后的编号列表===" + newNoList);
                List<Map> allList = new ArrayList<>();
                List<Map> allListB = new ArrayList<>();
                List<Map> allListC = new ArrayList<>();
                // 要把原来的数据根据编号进行分组过滤

                List<Map> objList1 = new ArrayList<>();


                List<Map> objListA = new ArrayList<>();
                for (String no : newNoList) {
                    List<Map> objList = new ArrayList<>();
                    List<Map> objListB = new ArrayList<>();
                    List<Map> objListC = new ArrayList<>();
                    // 合计值
                    BigDecimal total = new BigDecimal("0");
                    BigDecimal totalB = new BigDecimal("0");
                    BigDecimal totalC = new BigDecimal("0");
                    // 序号
                    int num = 0;
                    int num1 = 0;
                    int num2 = 0;
                    for (Object objects : dataList) {
                        //   Map jsonObject = JSON.parseObject(objects.toString());
                        Map jsonObject = JSON.parseObject(objects.toString());

                        Map row1 = new LinkedHashMap();
                        Map row2 = new LinkedHashMap();
                        Map row3 = new LinkedHashMap();
                        //    Map<String, Object> row1 = new LinkedHashMap<>();
//                        Map<String, Object> row11 = new LinkedHashMap<>();
//                        Map<String, Object> row2 = new LinkedHashMap<>();
//                        Map<String, Object> row3 = new LinkedHashMap<>();

                        String terNumber = jsonObject.get("终端号").toString();
                        String isBank = jsonObject.get("发卡行").toString();
                        String tranAmout = jsonObject.get("交易金额").toString();
                        String tradeType = jsonObject.get("交易类型").toString();
                        String carsds = jsonObject.get("卡种").toString();
                        String cardNumber = jsonObject.get("序列号").toString();
                        String tradeDate = jsonObject.get("交易日期").toString();
                        String tradingHour = jsonObject.get("交易时间").toString();
                        String authCode = jsonObject.get("授权号").toString();
                        String tip = jsonObject.get("小费").toString();
                        String inPeriods = jsonObject.get("分期期数").toString();
                        String bankCharges = jsonObject.get("银行手续费").toString();
                        String refundComm = jsonObject.get("DCC返还手续费").toString();
                        String arrivalAmout = jsonObject.get("划账金额").toString();
                        String voucherNumber = jsonObject.get("凭证号").toString();
                        String bathNumber = jsonObject.get("批次号").toString();
                        String tranNumber = jsonObject.get("POS交易序号").toString();
                        String settAccount = jsonObject.get("结算账号").toString();
                        String orderNumber = jsonObject.get("订单号").toString();
                        String counterNumber = jsonObject.get("柜台编号").toString();
                        String systemNumber = jsonObject.get("系统参考号").toString();
                        String cardName = jsonObject.get("持卡人姓名").toString();
                        String PayMent = jsonObject.get("付款凭证号").toString();


                        if (terNumber.equals(no)) {
                            String amount = jsonObject.get("交易金额").toString();
                            switch (tradeType) {
                                case "消费":
                                case "消费撤销":
                                case "联机退货":
                                    num++;
                                    if (!terNumber.contains("终端号")) {
                                        row1.put("序号", num);
                                        row1.put("终端号", terNumber);
                                        row1.put("发卡行", isBank);
                                        row1.put("交易金额", tranAmout);
                                        row1.put("交易类型", tradeType);
                                        row1.put("卡种", carsds);
                                        row1.put("卡号-序列号", cardNumber);
                                        row1.put("交易日期", tradeDate);
                                        row1.put("交易时间", tradingHour);
                                        row1.put("授权号", authCode);
                                        row1.put("小费", tip);
                                        row1.put("分期期数", inPeriods);
                                        row1.put("银行手续费", bankCharges);
                                        row1.put("DCC返还手续费", refundComm);
                                        row1.put("划账金额", arrivalAmout);
                                        row1.put("凭证号", voucherNumber);
                                        row1.put("批次号", bathNumber);
                                        row1.put("POS交易序号", tranNumber);
                                        row1.put("结算账号", settAccount);
                                        row1.put("订单号", orderNumber);
                                        row1.put("柜台编号", counterNumber);
                                        row1.put("系统参考号", systemNumber);
                                        row1.put("持卡人姓名", cardName);
                                        row1.put("付款凭证号", PayMent);

                                    }
                                    total = total.add(new BigDecimal(amount));
                                    objList.add(row1);
                                    break;
                                case "银联二维码消费(被扫)":
                                case "银联二维码退货":
                                    //row2.put("序号", num);
                                    num2++;
                                    if (!terNumber.contains("终端号")) {
                                        row2.put("序号", num2);
                                        row2.put("终端号", terNumber);
                                        row2.put("发卡行", isBank);
                                        row2.put("交易金额", tranAmout);
                                        row2.put("交易类型", tradeType);
                                        row2.put("卡种", carsds);
                                        row2.put("卡号-序列号", cardNumber);
                                        row2.put("交易日期", tradeDate);
                                        row2.put("交易时间", tradingHour);
                                        row2.put("授权号", authCode);
                                        row2.put("小费", tip);
                                        row2.put("分期期数", inPeriods);
                                        row2.put("银行手续费", bankCharges);
                                        row2.put("DCC返还手续费", refundComm);
                                        row2.put("划账金额", arrivalAmout);
                                        row2.put("凭证号", voucherNumber);
                                        row2.put("批次号", bathNumber);
                                        row2.put("POS交易序号", tranNumber);
                                        row2.put("结算账号", settAccount);
                                        row2.put("订单号", orderNumber);
                                        row2.put("柜台编号", counterNumber);
                                        row2.put("系统参考号", systemNumber);
                                        row2.put("持卡人姓名", cardName);
                                        row2.put("付款凭证号", PayMent);
                                    }
                                    totalB = totalB.add(new BigDecimal(amount));
                                    objListB.add(row2);
                                    break;
                                case "外卡消费":
                                    num1++;
                                    //  row3.put("序号", num);
                                    if (!terNumber.contains("终端号")) {
                                        row3.put("序号", num1);
                                        row3.put("终端号", terNumber);
                                        row3.put("发卡行", isBank);
                                        row3.put("交易金额", tranAmout);
                                        row3.put("交易类型", tradeType);
                                        row3.put("卡种", carsds);
                                        row3.put("卡号-序列号", cardNumber);
                                        row3.put("交易日期", tradeDate);
                                        row3.put("交易时间", tradingHour);
                                        row3.put("授权号", authCode);
                                        row3.put("小费", tip);
                                        row3.put("分期期数", inPeriods);
                                        row3.put("银行手续费", bankCharges);
                                        row3.put("DCC返还手续费", refundComm);
                                        row3.put("划账金额", arrivalAmout);
                                        row3.put("凭证号", voucherNumber);
                                        row3.put("批次号", bathNumber);
                                        row3.put("POS交易序号", tranNumber);
                                        row3.put("结算账号", settAccount);
                                        row3.put("订单号", orderNumber);
                                        row3.put("柜台编号", counterNumber);
                                        row3.put("系统参考号", systemNumber);
                                        row3.put("持卡人姓名", cardName);
                                        row3.put("付款凭证号", PayMent);
                                    }
                                    totalC = totalC.add(new BigDecimal(amount));
                                    objListC.add(row3);
                                    break;
                            }

                            //     objList.add(row1);
                            // 计算合计

                            // 序号
                            i++;

                        }
                    }

                    if (objList.size() > 0) {
//                        Map totalMap = new HashMap<>();
//                        totalMap.put("序号", "合计");
//                        totalMap.put("终端号", "");
//                        totalMap.put("发卡行", "");
//                        totalMap.put("交易金额", total);
                        //   objList.add(totalMap);
                        allList.addAll(objList);
                    }


                    if (objListB.size() > 0) {
//                        Map totalMap = new HashMap<>();
//                        totalMap.put("序号", "合计");
//                        totalMap.put("终端号", "");
//                        totalMap.put("发卡行", "");
//                        totalMap.put("交易金额", totalB);
                        // objListB.add(totalMap);
                        allListB.addAll(objListB);

                    }

                    if (objListC.size() > 0) {
//                        Map totalMap = new HashMap<>();
//                        totalMap.put("序号", "合计");
//                        totalMap.put("终端号", "");
//                        totalMap.put("发卡行", "");
//                        totalMap.put("交易金额", totalC);
                        //  objListC.add(totalMap);
                        allListC.addAll(objListC);
                    }
                }

                if (allList.size() > 0) {
//                    Map totalMap = new HashMap<>();
//                    totalMap.put("序号", "总计合约");
//                    totalMap.put("终端号", "");
//                    totalMap.put("发卡行", "");
//                    totalMap.put("交易金额", totaA);
                    // objListA.add(totalMap);
                    allList.addAll(objListA);
                    exportExcel1(allList, "建设银行国内卡", header);
                }
                if (allListB.size() > 0) {
//                    Map totalMap = new HashMap<>();
//                    totalMap.put("序号", "总计合约");
//                    totalMap.put("终端号", "");
//                    totalMap.put("发卡行", "");
//                    totalMap.put("交易金额", totaB);
                    // allListB.add(totalMap);
                    allListB.addAll(allListB);
                    //    System.out.println("根据编号分组之后的数据" + allList);
                    // 导出
                    exportExcel1(allListB, "建设银行银联二维码", header);
                }
                if (allListC.size() > 0) {
//                    Map totalMap = new HashMap<>();
//                    totalMap.put("序号", "总计合约");
//                    totalMap.put("终端号", "");
//                    totalMap.put("发卡行", "");
//                    totalMap.put("交易金额", totaC);
                    //allListC.add(totalMap);
                    allListC.addAll(allListC);
                    // 导出
                    exportExcel1(allListC, "建设银行国外卡", header);
                }


            }
        }
        return resultMap;
    }


    private static void exportExcel1(List<Map> list, String fileName, String header) {
        Date date = new Date();

        String path = "home/bank/bankdata";
        String path1 = " E:\\桌面\\新建文件夹(4)\\";
        ExcelWriter writer = ExcelUtil.getWriter(path + exportDate(date) + fileName + ".xlsx");

        writer.setOnlyAlias(true);
// 合并单元格后的标题行，使用默认标题样式
        writer.merge(3, header);
// 一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);
        // 关闭writer，释放内存
        writer.close();
        System.out.println("执行完了导出在" + path);

    }


    //获取当下时间
    private static String exportDate(Date date) {

        SimpleDateFormat sdf = new SimpleDateFormat();// 格式化时间
        sdf.applyPattern("yyyyMMdd");
        System.out.println("现在时间：" + sdf.format(date)); //
        return sdf.format(date);
    }


    //  @RequestMapping("/exportExcel")
    private static void exportExcel11(HttpServletResponse response, List<Map> list, String fileName, String header) {
        Date date = new Date();
        String path = "home/bank/bankdata";
        String path1 = " E:\\桌面\\新建文件夹(4)\\";
        try {
            response.setContentType("application/octet-stream");
            //设置文件夹名称
            response.setHeader("Content-disposition", "attachment;fileName=" + exportDate(date) + fileName + ".xls");
            OutputStream out = response.getOutputStream();
            //用工具类创建writer  默认xls格式
            ExcelWriter writer = ExcelUtil.getWriter();
            writer.write(list);
//            writer.merge(3, header);
            writer.flush(out);
            writer.close();
            out.flush();
            System.out.println("执行完了");
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("异常" + e.getMessage());
        }

    }

//todo++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


    private static void exportExcel22(HttpServletResponse response, List<Map> list, String fileName, String header) throws IOException {
        List<ByteArrayOutputStream> bosList = new ArrayList<ByteArrayOutputStream>();
        List<String> excelName = new ArrayList<String>();
        //这里循环取出service里填充的单个excel流和单个excelName,如果多个循环即可
        bosList.add((ByteArrayOutputStream) list.get(1));
        //创建HttpServerResponse的输出流
        OutputStream out = response.getOutputStream();
        //创建写入流
        BufferedInputStream bis;
        //创建要写入的文件
        File file = new File("student.zip");
        //通过ZipOutputStream定义要写入的对象
        ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(file));
        //调取service层写入循环excle的方法，将流输入
        writeZos(bosList, zos, header);
        zos.close();
        response.setContentType("text/html; charset=UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode("student.zip", "UTF-8"))));
        bis = new BufferedInputStream(new FileInputStream("student.zip"));
        //定义byte，长度就是要转成zip文件的byte长度，避免浪费资源
        byte[] buffer = new byte[bis.available()];
        bis.read(buffer);
        out.flush();
        out.write(buffer);

    }

    //循环导入excel的流，准备在Controller层生成zip包
    public static void writeZos(List<ByteArrayOutputStream> bosList, ZipOutputStream zos, String excelName) throws IOException {
        for (int i = 0; i < bosList.size(); i++) {
            //将多个excel都转成字节流写入
            zos.putNextEntry(new ZipEntry(excelName + i));
            byte[] excelStream = bosList.get(i).toByteArray();
            zos.write(excelStream);
            //记得关闭
            zos.closeEntry();
        }


//        Date date = new Date();
//        String path = "home/bank/bankdata";
//        String path1 = " E:\\桌面\\新建文件夹(4)\\";
//        try {
//
//            //response 输出流
//            ServletOutputStream out = response.getOutputStream();
//            //压缩输出流
//            ZipOutputStream zipOutputStream = new ZipOutputStream(out);
//
//            HSSFWorkbook wb = new HSSFWorkbook();
//            //设置文件夹名称
//            HSSFSheet sheet = wb.createSheet(fileName);
//            HSSFRow row = sheet.createRow(0);
//            HSSFCell cell = row.createCell(0);
//            cell.setCellValue("内容" + 1);
//
//
//            response.setContentType("application/octet-stream");
//            response.setHeader("Content-disposition", "attachment;fileName=" + exportDate(date) + fileName + ".xls");
//           // OutputStream out = response.getOutputStream();
//            //用工具类创建writer  默认xls格式
//            ExcelWriter writer = ExcelUtil.getWriter();
//            writer.write(list);
////            writer.merge(3, header);
//            writer.flush(out);
//            writer.close();
//            out.flush();
//            System.out.println("执行完了");
//
//
//            //重点开始,创建压缩文件
//            ZipEntry z = new ZipEntry("压缩110"+ ".xls");
//            zipOutputStream.putNextEntry(z);
//            //写入一个压缩文件
//            wb.write(zipOutputStream);
//
//
//
//
//        } catch (IOException e) {
//            e.printStackTrace();
//            System.out.println("异常" + e.getMessage());
//        }

    }


    @RequestMapping(value = "/to", method = RequestMethod.GET)
    @ResponseBody
    public String toDownload(HttpServletResponse response, List<Map> rows, String fileName) {
        System.out.println("666");
        try {
//            List<Map> rows = new ArrayList<>();
//            Random r = new Random();
//            for (int i = 0; i < 10; i++) {
//
//                Map<String, Object> map = new HashMap<>();
//                map.put("ID", i + 1);
//                map.put("名字", "的去" + i);
//                map.put("年龄", i + 18);
//                rows.add(map);
//            }
            exportExcel11(response, rows, fileName, "");

            System.out.println("执行完了");

        } catch (Exception e) {
            e.printStackTrace();
        }
        return "1";

    }


    @RequestMapping(value = "/po")
    public void poizip(HttpServletResponse response) throws IOException {
        //response 输出流
        ServletOutputStream out = response.getOutputStream();
        //压缩输出流
        ZipOutputStream zipOutputStream = new ZipOutputStream(out);
        try {
            for (int i = 0; i < 6; i++) {
                //创建工作簿
                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFSheet sheet = wb.createSheet("sheet" + i);
                HSSFRow row = sheet.createRow(0);
                HSSFCell cell = row.createCell(0);
                cell.setCellValue("内容" + i);
                response.setContentType("application/octet-stream; charset=utf-8");
                response.setHeader("Content-Disposition", "attachment; filename=" + 88888 + "测试.zip");
                //重点开始,创建压缩文件
                ZipEntry z = new ZipEntry(i + ".xls");
                zipOutputStream.putNextEntry(z);
                //写入一个压缩文件
                wb.write(zipOutputStream);
            }
            zipOutputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            //注意关闭顺序，否则可能文件错误
            if (zipOutputStream != null) {
                zipOutputStream.close();
            }
            if (out != null) {
                out.close();
            }
        }
    }


//    public void t(){
//        File zipFile = BusinessUtils.zipFile(workBook, xworkWork, fileNames);
//        String downloadFileName = URLEncoder.encode("XX商户XX公司XXXX报税单销售汇总信息.zip", "UTF-8");
//        response.setHeader("content-type", "application/octet-stream");
//        response.setContentType("application/x-execl");
//        response.setHeader("Content-Disposition",
//                "attachment;filename=" + new String((downloadFileName).getBytes(), "UTF-8"));
//        // 读取文件
//        InputStream in = new FileInputStream(zipFile);
//        ServletOutputStream outputStream = response.getOutputStream();
//        // 写文件
//        int b;
//        while ((b = in.read()) != -1) {
//            outputStream.write(b);
//        }
//        in.close();
//        outputStream.close();
//        //删除临时文件
//        zipFile.delete();
//    }
}
